{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "db = sqlite3.connect(\"name_database.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a database called name_database.db\n",
    "# add one table to the database called names_table\n",
    "# add columns to the database table: Id, first_name, last_name, age \n",
    "conn = sqlite3.connect('name_database.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"\"\"CREATE TABLE IF NOT EXISTS names_table (\n",
    "    Id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "    first_name text,\n",
    "    last_name text,\n",
    "    age integer\n",
    "    )\"\"\")\n",
    "conn.commit()\n",
    "\n",
    "cur.close()\n",
    "conn.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('name_database.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"INSERT INTO names_table VALUES(:Id, :first_name, :last_name, :age)\",\n",
    "              {'Id': None,\n",
    "               'first_name': 'Gabriella',\n",
    "               'last_name': 'Louise',\n",
    "               'age': int(8)\n",
    "              })\n",
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Gabriella', 'Louise', 8, 1)\n"
     ]
    }
   ],
   "source": [
    "conn = sqlite3.connect('name_database.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"SELECT first_name, last_name, age, MAX(rowid) FROM names_table\")\n",
    "record = cur.fetchone()\n",
    "print(record)\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('name_database.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"INSERT INTO names_table VALUES(:Id, :first_name, :last_name, :age)\",\n",
    "              {'Id': None,\n",
    "               'first_name': 'Maelle',\n",
    "               'last_name': 'Levin',\n",
    "               'age': int(5)\n",
    "              })\n",
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Maelle', 'Levin', 5, 2)\n"
     ]
    }
   ],
   "source": [
    "conn = sqlite3.connect('name_database.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"SELECT first_name, last_name, age, MAX(rowid) FROM names_table\")\n",
    "record = cur.fetchone()\n",
    "print(record)\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 网页使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import datetime\n",
    "import pytz\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.isfile('data.db'):\n",
    "    conn = sqlite3.connect('data.db')\n",
    "    c = conn.cursor()\n",
    "    c.execute(\"\"\"CREATE TABLE data (\n",
    "        Id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        API_key text,\n",
    "        date_time text,\n",
    "        mac text,\n",
    "        field integer,\n",
    "        data real\n",
    "        )\"\"\")\n",
    "    conn.commit()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_save(api_key,mac,field,data):\n",
    "    conn = sqlite3.connect('data.db')\n",
    "    c = conn.cursor()\n",
    "    t = datetime.datetime.now(tz=pytz.utc)\n",
    "    date_time_str = t.isoformat()\n",
    "    c.execute(\"INSERT INTO data VALUES(:Id, :API_key, :date_time, :mac, :field, :data)\",\n",
    "            {'Id': None, 'API_key': api_key, 'date_time': date_time_str, 'mac': mac, 'field': int(field),\n",
    "            'data': round(float(data), 4)})\n",
    "    conn.commit()\n",
    "    c.close()\n",
    "    conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "no such table: data",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_16220/2194161370.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# date_save('DTLZ3LBY','5m:ct:7f:3b:0d:a8',2,15.2)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdate_save\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'DTLZ3LBY'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'5m:ct:7f:3b:0d:a8'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m3\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m15.2\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mdate_save\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'DTLZ3LBY'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'5m:ct:7f:3b:0d:a8'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m4\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m15.2\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mdate_save\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'DTLZ3LBY'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'5m:ct:7f:3b:0d:a8'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m5\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m15.2\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_16220/1212471059.py\u001b[0m in \u001b[0;36mdate_save\u001b[1;34m(api_key, mac, field, data)\u001b[0m\n\u001b[0;32m      6\u001b[0m     c.execute(\"INSERT INTO data VALUES(:Id, :API_key, :date_time, :mac, :field, :data)\",\n\u001b[0;32m      7\u001b[0m             {'Id': None, 'API_key': api_key, 'date_time': date_time_str, 'mac': mac, 'field': int(field),\n\u001b[1;32m----> 8\u001b[1;33m             'data': round(float(data), 4)})\n\u001b[0m\u001b[0;32m      9\u001b[0m     \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m     \u001b[0mc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mOperationalError\u001b[0m: no such table: data"
     ]
    }
   ],
   "source": [
    "# date_save('DTLZ3LBY','5m:ct:7f:3b:0d:a8',2,15.2)\n",
    "date_save('DTLZ3LBY','5m:ct:7f:3b:0d:a8',3,15.2)\n",
    "date_save('DTLZ3LBY','5m:ct:7f:3b:0d:a8',4,15.2)\n",
    "date_save('DTLZ3LBY','5m:ct:7f:3b:0d:a8',5,15.2)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| rowid  | api_key|   mac  | field |   data |  \n",
    "| ------ | ------ | ------ |------ | ------ |\n",
    "|   1    |DTLZ3LBY|  |\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None (4,)\n"
     ]
    }
   ],
   "source": [
    "# 查询\n",
    "conn = sqlite3.connect('data.db')\n",
    "c = conn.cursor()\n",
    "max_id = c.execute(\"SELECT MAX(rowid) FROM data \").fetchone()\n",
    "# sql = \"SELECT data, date_time, field FROM data where rowid = %s\"%(2)\n",
    "# c.execute(sql)  #最新一条\n",
    "c.execute(\"delete from data where rowid = 4\")  #第一条\n",
    "row1 = c.fetchone()\n",
    "print(row1,max_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "no such table: data",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_16220/2986226841.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     10\u001b[0m         \u001b[0mtem\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mi\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchone\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m  \u001b[1;31m#最新一条\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mtem\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 12\u001b[1;33m \u001b[0mselect_3data\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_16220/2986226841.py\u001b[0m in \u001b[0;36mselect_3data\u001b[1;34m()\u001b[0m\n\u001b[0;32m      4\u001b[0m     \u001b[0mconn\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msqlite3\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'data.db'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m     \u001b[0mc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 6\u001b[1;33m     \u001b[0mmax_id\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"SELECT MAX(rowid) FROM data \"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchone\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      7\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      8\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mi\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mrange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m3\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mOperationalError\u001b[0m: no such table: data"
     ]
    }
   ],
   "source": [
    "# 查询最新3条\n",
    "def select_3data():\n",
    "    tem = {}\n",
    "    conn = sqlite3.connect('data.db')\n",
    "    c = conn.cursor()\n",
    "    max_id = c.execute(\"SELECT MAX(rowid) FROM data \").fetchone()[0]\n",
    "\n",
    "    for i in range(3):\n",
    "        sql = \"SELECT data, date_time, field FROM data where rowid = %s\"%(max_id-i)\n",
    "        tem[i] = c.execute(sql).fetchone()  #最新一条\n",
    "    return tem\n",
    "select_3data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "0",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_2140/2988063692.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mtem\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m: 0"
     ]
    }
   ],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "a23257344542c70b70a498512f46db94a3a7f44d371a6893c689768adca66338"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
